package dao.impl;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import dao.GoodsDao;
import dbpool.DbPool;
import entity.Goods;

public class GoodsDaoImpl implements GoodsDao {
	DbPool pool = DbPool.getPool();
	/**
	 * 获得商品列表
	 */
	@Override
	public List<Goods> getgoodsList() throws SQLException {
		Connection conn = pool.getConn();
		System.out.println(conn);
		String sql = "select * from goods";
		QueryRunner runner = new QueryRunner();
		List<Goods> goodsList = (List<Goods>) runner.query(conn, sql, new BeanListHandler<Goods>(Goods.class));
		pool.back(conn);
		return goodsList;
	}
	/**
	 * 根据商品名搜索商品
	 */
	@Override
	public List<Goods> getgoods(String goods_name) throws SQLException {
		Connection conn = pool.getConn();
		System.out.println(conn);
		String sql = "select * from goods" + " where goods_name like \"%\"?\"%\" ";
		QueryRunner runner = new QueryRunner();
		List<Goods> goodsList = (List<Goods>) runner.query(conn, sql, new BeanListHandler<Goods>(Goods.class),goods_name);
		pool.back(conn);
		return goodsList;
	}
	/**
	 * 新增商品
	 */
	@Override
	public int addgoods(String goods_name, String goods_img, String goods_type, String goods_price, String goods_limit)
			throws SQLException {
		Connection conn = pool.getConn();
		String sql = "insert into goods values(null,?,?,?,?,?)";
		QueryRunner runner = new QueryRunner();
		int res = runner.update(conn, sql, goods_name,goods_img,goods_type,goods_price,goods_limit);
		pool.back(conn);
		return res;

	}
	/**
	 * 删除商品
	 */
	@Override
	public int delgoods(String goods_name) throws SQLException {
		Connection conn = pool.getConn();
		String sql = "delete from goods where goods_name = ?";
		QueryRunner runner = new QueryRunner();
		int res = runner.update(conn, sql, goods_name);
		pool.back(conn);
		return res;
	}
	//修改价格
	@Override
	public int updataprice(String name, String price) throws SQLException {
		Connection conn = pool.getConn();
		String sql = "update goods set goods_price = ? where goods_name=?";
		QueryRunner runner = new QueryRunner();
		int res = runner.update(conn, sql, price,name);
		pool.back(conn);
		return res;
	}
	//根据商品id获取实体
	@Override
	public Goods getGoodsById(int goods_id) throws SQLException {
		Connection conn = pool.getConn();
		String sql = "select * from goods where goods_id = ?";
		QueryRunner runner = new QueryRunner();
		Goods good = (Goods)runner.query(conn, sql, new BeanHandler<Goods>(Goods.class), goods_id);
		pool.back(conn);
		return good;
	}
	/**
	 * 分页查询
	 */
	@Override
	public List<Goods> getPage(int page) throws SQLException {
		Connection conn = pool.getConn();
		String sql = "select * from goods limit ?,10";
		QueryRunner runner = new QueryRunner();
		List<Goods> goodList = (List<Goods>)runner.query(conn, sql, new BeanListHandler<Goods>(Goods.class), (page-1)*10);
		pool.back(conn);
		return goodList;
	}
	/**
	 * 商品总数
	 */
	@Override
	public int getCount() throws SQLException {
		String sql = "select count(*) from goods";
		try {
			Connection conn = pool.getConn();

			QueryRunner runner = new QueryRunner();

			Object query = runner.query(conn, sql, new ScalarHandler(1));
			pool.back(conn);
			return Integer.parseInt(String.valueOf(query));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return 0;
	}

}
